package com.supermap.dataservice.cloud.agrinfo;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;

import java.util.List;
import java.util.Map;

/**
 * @author aqua
 * @description 农情
 * @date 2018年11月16 上午 10:24
 * @email 1301584033@qq.com
 */
@Mapper
public interface AgrinfoCloudMapper  {
    /**
     * 农情详情展示
     * */
    @Select("select  \n" +
            "t1.id,\n" +
            "t1.descript,\n" +
            "t2.showName,\n" +
            "t2.HeadImage,\n" +
            " date_format(t1.createtime,'%Y-%m-%d %H:%i:%S') as create_date,\n" +
            " group_concat(t3.imageURLContent) as imageURLContent from \n" +
            "(select t1.id,t1.userId,t1.descript,t1.createtime from t_agrinfo t1 where t1.userId=#{userId}) t1\n" +
            "inner join t_user\tt2\n" +
            "on t1.userId =t2.id\n" +
            "Left join t_agrinfoimg t3\n" +
            "on t1.id=t3.agrInfoId " +
            " group by t1.id " +
            "order by t1.createtime desc" +
            " limit #{pageNum},#{pageSize}")
    List<Map<String,Object>> getAgrinfoDetailsByUserId(@Param("userId")String userId,@Param("pageNum")Integer pageNum,@Param("pageSize")Integer pageSize);

    @Select("select  \n" +
            " count(1) from \n" +
            "(select t1.id,t1.userId,t1.descript,t1.createtime from t_agrinfo t1 where t1.userId=#{userId}) t1\n" +
            "inner join t_user\tt2\n" +
            "on t1.userId =t2.id\n" +
            "Left join t_agrinfoimg t3\n" +
            "on t1.id=t3.agrInfoId " +
            "order by t1.createtime desc")
    int getAgrinfoDetailsByUserIdByCount(@Param("userId")String userId);



    /**
     * 灾情统计数
     * */
    @Select("select \n" +
            "t1.id as disaId,\n" +
            "t1.descript,\n" +
            "t2.showName,\n" +
            "t2.HeadImage,\n" +
            "date_format(t1.uploadTime,'%Y-%m-%d %H:%i:%S') as create_date,\n" +
            "group_concat(distinct t3.imageURLContent) as imgs\n" +
            " from t_disaster t1\n" +
            " inner join t_user t2\n" +
            " on t1.userId=t2.id and t1.userId=#{userId}\n" +
            " Left join t_disasterimg t3\n" +
            " on t1.id=t3.disasterId\n" +
            " group by t1.id\n" +
            " order by t1.uploadTime desc\n" +
            " limit #{pageNum},#{pageSize}")
    List<Map<String,Object>>  getPageDisasterListByUserID(@Param("userId")String userId,@Param("pageNum")Integer pageNum,@Param("pageSize")Integer pageSize);

    @Select("select \n" +
            "count(1)\n" +
            " from t_disaster t1\n" +
            " inner join t_user t2\n" +
            " on t1.userId=t2.id and t1.userId=#{userId}\n" +
            " Left join t_disasterimg t3\n" +
            " on t1.id=t3.disasterId ")
    int  getPageDisasterListByUserIDCount(@Param("userId")String userId);




    /**
     * 农事统计数
     * */
    @Select("select \n" +
            "t1.id ,\n" +
            "t1.farmlandid as farmId,\n" +
            "t1.description,\n" +
            "t2.showName,\n" +
            "t2.HeadImage,\n" +
            "t3.name as typeName,\n" +
            "date_format(t1.createtime,'%Y-%m-%d %H:%i:%S') as create_date,\n" +
            " replace(replace(replace(t1.img_url,'[\"',\"\"),'\"]',\"\"),'\"',\"\")  as imgs\n" +
            " from t_farming t1\n" +
            " inner join t_user t2\n" +
            " on t1.userId=t2.id and t1.farmlandid=#{farmId}\n" +
            " inner join t_farmingtype t3 on t1.`type`= t3.code" +
            " order by t1.createtime desc\n" +
            " limit #{pageNum},#{pageSize}")
    List<Map<String,Object>>  getFarmingDetailsByFarmId(@Param("farmId")String farmId,@Param("pageNum")Integer pageNum,@Param("pageSize")Integer pageSize);

    @Select("select \n" +
            "count(1)\n" +
            " from t_farming t1 where t1.farmlandid=#{farmId}")
    int  getFarmingDetailsByFarmIdCount(@Param("farmId")String farmId);



    /**
     * 农情分布展示
     * */
    @SelectProvider(type=AgrinfoCloudMapperSQL.class,method = "getAgrinfoByareaCodeAndbreedSQL")
    List<Map<String,Object>> getAgrinfoByareaCodeAndbreed(@Param("areaCode")String areaCode,@Param("cropBreedId") String cropBreedId);
    /**
     * 灾情分布展示
     * */
    @SelectProvider(type=AgrinfoCloudMapperSQL.class,method = "getDisasterByareaCodeAndbreedSQL")
    List<Map<String,Object>> getDisasterByareaCodeAndbreed(@Param("areaCode")String areaCode,@Param("cropBreedId")String cropBreedId,@Param("disatype")String disatype);
    /**
     * 农事分布展示
     * */
    @SelectProvider(type=AgrinfoCloudMapperSQL.class,method = "getFarmingByareaCodeAndbreedSQL")
    List<Map<String,Object>> getFarmingByareaCodeAndbreed(@Param("areaCode")String areaCode,@Param("cropBreedId") String cropBreedId);

    class AgrinfoCloudMapperSQL{
        public String getFarmingByareaCodeAndbreedSQL(Map<String,Object> map){
            String areaCode= (String)map.get("areaCode");
            String cropBreedId= (String)map.get("cropBreedId");
            StringBuilder stringBuilder=new StringBuilder();
            stringBuilder.append("select t1.id  as farmingId,t1.farmlandid as farmId,t1.longitude,t1.latitude,t1.userid from t_farming t1 \n" +
                    "    where  t1.areacode like concat("+areaCode+",'%') ");
            if(cropBreedId!=null){
                stringBuilder.append("and  t1.croptype="+cropBreedId+" ");
            }
            stringBuilder.append("group by longitude,latitude");
            return stringBuilder.toString();
        }
        public String getAgrinfoByareaCodeAndbreedSQL(Map<String,Object> map){
            String areaCode= (String)map.get("areaCode");
            String cropBreedId= (String)map.get("cropBreedId");
            StringBuilder stringBuilder=new StringBuilder();
            stringBuilder.append("select t1.id as agrinfoId,t1.longitude,t1.latitude,t1.userId from t_agrinfo t1 \n" +
                    "    where  t1.areacode like concat("+areaCode+",'%') ");
            if(cropBreedId!=null){
                stringBuilder.append("and  t1.breed="+cropBreedId+" ");
            }
            stringBuilder.append("group by longitude,latitude");
            return stringBuilder.toString();
        }

        public String getDisasterByareaCodeAndbreedSQL(Map<String,Object> map){
            String areaCode= (String)map.get("areaCode");
            String cropBreedId= (String)map.get("cropBreedId");
            String disatype=(String)map.get("disatype");
            StringBuilder stringBuilder=new StringBuilder();
            stringBuilder.append("select t1.id as disasterId,t1.longitude,t1.latitude,t1.userId from t_disaster t1 \n" +
                    "    where  t1.areacode like concat("+areaCode+",'%') ");
            if(cropBreedId!=null){
                stringBuilder.append("and  t1.breed="+cropBreedId+" ");
            }
            if(disatype!=null){
                stringBuilder.append("and  t1.disastertype="+disatype+" ");
            }
            stringBuilder.append("group by longitude,latitude");
            return stringBuilder.toString();
        }
    }
}
